# demo01_mysql.py
import pymysql
# 创建mysql连接
conn = pymysql.connect(
    host='192.168.88.100',
    port=3306,
    user='root',
    password='123456',
    db='tedu',
    charset='utf8'
)
# 创建游标，用于执行SQL命令
cur = conn.cursor()
# 执行SQL命令
"""
# 1.创建数据表
# 部门表dept(编号、部门名称)
create_sql = '''
    create table dept(
        did int, 
        dname varchar(16)
    );
'''
# 执行命令
cur.execute(create_sql)

# 2.插入数据
# insert_sql = 'insert into dept values(1, "研发部")'
# cur.execute(insert_sql)
# 将命令作为一个模板，每次执行命令的时候只需要为模板提供一组值
insert_sql = 'insert into dept values(%s, %s)'
# 执行一次
# cur.execute(insert_sql, (2, '运维部'))
# 执行多次
cur.executemany(insert_sql, [(3, '测试部'), (4, '设计部'), (5, '运营部')])

# 3.查询数据
select_sql = 'select * from dept'
cur.execute(select_sql)
# 通过获取查询的数据
one = cur.fetchone()  # 取出一条
print(one)
many = cur.fetchmany(2) # 取出多条
print(many)
all = cur.fetchall()  # 取出所有
print(all)

# 4.修改数据
update_sql = 'update dept set dname=%s where did=%s'
# cur.execute(update_sql, ('财务部', 1))
cur.executemany(update_sql, [('人力部', 2), ('后勤部', 3)])
"""
# 5.删除数据
delete_sql = 'delete from dept where did=%s'
# cur.execute(delete_sql, (2, ))
cur.executemany(delete_sql, [(1, ), (4, )])

# 提交事务：只要数据发生变化，必须提交事务
conn.commit()
# 关闭游标
cur.close()
# 关闭连接
conn.close()





